package org.ftc.util;

import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;

import jxl.Sheet; 
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet; 
import jxl.write.WritableWorkbook;

public class XlsUtil {
	
	public List<HashMap<String, String>> readXls(InputStream is){
		HashMap<Integer, String> column = new HashMap<Integer, String>();
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		Workbook wb = null;
		try { 
			wb = Workbook.getWorkbook(is);
			Sheet sheet = wb.getSheet(0);
			//���W��
			for(int c = 0 ; c < sheet.getColumns(); c++)
				column.put(c, sheet.getCell(c, 0).getContents().trim() );
			//���e
			for (int i = 1; i < sheet.getRows(); i++) {
				HashMap<String, String> sheetMap = new HashMap<String, String>();
				for (int c = 0; c < column.size(); c++) {
					sheetMap.put(column.get(c), sheet.getCell(c, i).getContents());
				}
				list.add(sheetMap);
			}
			wb.close();
			wb = null;
			is.close();
			is = null;
			System.gc();
			return list;
		}catch ( Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<HashMap<String, String>> readXls(File file){
		HashMap<Integer, String> column = new HashMap<Integer, String>();
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		Workbook wb = null;
		try { 
			wb = Workbook.getWorkbook(file);
			Sheet sheet = wb.getSheet(0);
			//���W��
			for(int c = 0 ; c < sheet.getColumns(); c++)
				column.put(c, sheet.getCell(c, 0).getContents().trim() );
			//���e
			for (int i = 1; i < sheet.getRows(); i++) {
				HashMap<String, String> sheetMap = new HashMap<String, String>();
				for (int c = 0; c < column.size(); c++) {
					sheetMap.put(column.get(c), sheet.getCell(c, i).getContents());
				}
				list.add(sheetMap);
			}
			wb.close();
			wb = null;
			System.gc();
			return list;
		}catch ( Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public void writeXls(List<LinkedHashMap<String, String>> list,  File file  ){
		try {
			WritableWorkbook workbook = Workbook.createWorkbook(file);
			WritableSheet sheet = workbook.createSheet("Sheet", 0);
			
			WritableFont wf = new WritableFont(WritableFont.createFont("�з���"), 10);   
			WritableCellFormat wcfF = new WritableCellFormat(wf);
			wcfF.setAlignment(Alignment.CENTRE); // ����覡	
			
			if( list != null ){
				int x = 0;
				LinkedHashMap<String, String> colName = list.get(0);
				for(Iterator<String> it = colName.keySet().iterator(); it.hasNext();){
					sheet.addCell(new Label((x++), 0, it.next().toString() , wcfF));
				}
				
				for(int i = 0 ; i < list.size() ; i++){
					x = 0;
					LinkedHashMap<String, String> map = list.get(i);
					for(Iterator<String> it = colName.keySet().iterator(); it.hasNext();){
						String key = it.next().toString();
						sheet.addCell( new Label( (x++), i + 1 , map.get( key ), wcfF));
					} 
				}
			}
			workbook.write(); 
			workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}		
	}
	
	
	public static void main(String[] str){

	}
}
